package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Video;
import database.DBManager;

public class VideoDao {
	private String sqlread   	   = "SELECT * FROM video";
//	private String sqlGetName  	   = "SELECT * FROM video WHERE videoName = ?";
	private String sqlCreate   	   = "INSERT INTO video(videoId, description) VALUES(?, ?)";
//	private String sqlDelete   	   = "DELETE FROM video WHERE videoName = ?";
	private String sqlGetBetween   = "SELECT * FROM video WHERE videoName BETWEEN 	?,? ";
	private String sqlUpdate	   = "UPDATE video SET video.description = ? WHERE video.videoId = ?";
	
	private Connection        con      = null;
	private PreparedStatement psRead   = null;
//	private PreparedStatement psGetD   = null;
	private PreparedStatement psGetBetween   = null;
	private PreparedStatement psCreate = null;
//	private PreparedStatement psDelete = null;
	private PreparedStatement psUpdate = null;
	
	public VideoDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			this.psRead   = con.prepareStatement(sqlread);
//			this.psGetD   = con.prepareStatement(sqlGetName);
			this.psGetBetween   = con.prepareStatement(sqlGetBetween);
			this.psCreate = con.prepareStatement(sqlCreate);
//			this.psDelete = con.prepareStatement(sqlDelete);
			this.psUpdate = con.prepareStatement(sqlUpdate);
			
		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Video> getVideos(){
		ArrayList<Video> videos = new ArrayList<Video>();
		try {
			ResultSet rs = psRead.executeQuery();
			while(rs.next()){
				long name = rs.getLong("videoId");
				String description = rs.getString("description");
				videos.add(new Video(name, description));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
	
	public ArrayList<Video> getVideosByDate(int from, int to){
		ArrayList<Video> videos = new ArrayList<Video>();
		try {
			psGetBetween.clearParameters();
			psGetBetween.setInt(1, from);
			psGetBetween.setInt(2, to);
			ResultSet rs = psGetBetween.executeQuery();
			while(rs.next()){
				int name = rs.getInt("name");
				String description = rs.getString("description");
				videos.add(new Video(name, description));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return videos;
	}
	
	public boolean addVideo(long name, String description){
		boolean succeed = false;
		try {
			psCreate.clearParameters();
			psCreate.setLong(1, name);
			psCreate.setString(2, description);
			if( psCreate.executeUpdate() != 0 ) {
				succeed = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return succeed;
		
	}
	
	public boolean editVideo(long name, String description){
		boolean succeed = false;
		try {
			psUpdate.clearParameters();
			psUpdate.setString(1, description);
			psUpdate.setLong(2, name);
			
			if( psUpdate.executeUpdate() != 0 ) {
				succeed = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return succeed;
		
	}


}
